/**
 * 
 */
package com.hcmu.dto;

import java.util.ArrayList;
import java.util.HashMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author SEVEN
 *
 */
public class DatabaseHelper {
	public static final int DATABASE_VERSION 	=	1;
	public static final String DATABASE_NAME 	=	"funnystory.db";
	
	//The Type of stories
	public static final String CATEGORY_TABLE 		= "categories";
	public static final String STORY_TABLE 				= "stories";
	public static final String ID 						= "id";
	public static final String TITLE 					= "title";
	//public static final String ICON 					= "icon";
	public static final String CONTENT 					= "content";
	public static final String CAT_ID 					= "cat_id";
	public static final String FAVOURITE 				= "favourite";
	public static final String NUM_READ 				= "num_read";
	public static final String NUM_LIKED 				= "num_liked";
	public static final String NUM_SHARED 				= "num_shared";
	
	//creating category table
	public static final String CREATE_CATEGORY_TABLE = "create table " + CATEGORY_TABLE + " ( "
			+ ID + " integer primary key autoincrement, "
			+ TITLE + " text not null "
			+ ")";
	
	//creating stories table
	public static final String CREATE_STORY_TABLE = "create table " + STORY_TABLE + " ( "
			+ ID + " integer primary key autoincrement, "
			+ TITLE + " text not null, "
			+ CAT_ID + " text not null, "
			+ CONTENT + " text not null, "
			+ FAVOURITE + " text, "
			+ NUM_READ + " text, "
			+ NUM_LIKED + " text, "
			+ NUM_SHARED + " text"
			+ " ) ";
	
	public Context _context;
	public SQLiteDatabase sqliteDatabase;
	public SQLiteStoryHelper sqliteStoryHelper;
	
	
	
	//DatabaseHelper constructor
	public DatabaseHelper(Context ct){
		_context = ct;
	}
	
	
	public SQLiteDatabase getDB(){
		return sqliteDatabase;
	}
		
		
	/**
	 * SQLiteStoryHelper class that provided a SQLite object which can read/write data in database 
	 * @author SEVEN
	 *
	 */
	public class SQLiteStoryHelper extends SQLiteOpenHelper{
		//constructor
		public SQLiteStoryHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_CATEGORY_TABLE);
			db.execSQL(CREATE_STORY_TABLE);
		}
		
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + CAT_ID);
			db.execSQL("DROP TABLE IF EXISTS " + STORY_TABLE);
		}
	}
	
	
	//open database to read
	public DatabaseHelper openToRead() throws android.database.SQLException{
		sqliteStoryHelper = new SQLiteStoryHelper(_context, DATABASE_NAME, null, DATABASE_VERSION);
		sqliteDatabase	=	sqliteStoryHelper.getReadableDatabase();
		return this;
	}
	
	
	//open database to write
	public DatabaseHelper openToWrite() throws android.database.SQLException{
		sqliteStoryHelper = new SQLiteStoryHelper(_context, DATABASE_NAME, null, DATABASE_VERSION);
		sqliteDatabase	=	sqliteStoryHelper.getWritableDatabase();
		return this;
	}
	
	
	public void close(){
		sqliteDatabase.close();
	}
	
	
	/*********************************************Category table********************************/
	
	public long insertCategory(String title){
		ContentValues contentValues = new ContentValues();
		contentValues.put(TITLE, 	title);
		return sqliteDatabase.insert(CATEGORY_TABLE, null, contentValues);
	}
	
	public void updateCategory(int id, String newTitle){
		ContentValues contentValues = new ContentValues();
		contentValues.put(TITLE, newTitle);
		
		String where = ID + "=" + id;
		sqliteDatabase.update(CATEGORY_TABLE , contentValues, where, null);
	}
	
	//Deleting a type
	public int deleteCategory(int id){
		return sqliteDatabase.delete(CATEGORY_TABLE, ID +" = "+id, null);
	}
	
	
	//Get title of the type
	public String getCategoryTitle(int id){
		String[] columns = new String[]{TITLE};
		String where = ID+"="+id;
		Cursor cursor = sqliteDatabase.query(CATEGORY_TABLE, columns, where, null, null, null, null);
		cursor.moveToFirst();
		return cursor.getString(0);
	}
	
	
		
	/**
	 * Lay ve id cua loai truyen cua 1 truyen khi biet id cua truyen
	 * @param sid : id cua truyen
	 * @return id cua loai truyen 
	 */
	public int getCategoryId(int sid){
		String[] columns = new String[]{CAT_ID};
		String where = ID+"="+sid;
		Cursor cursor = sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
		cursor.moveToFirst();
		return Integer.parseInt(cursor.getString(0));
	}
	
		
	//Get all title of the types
	public Cursor getAllCategory(){
		String[] columns = new String[]{ID, TITLE};
		Cursor cursor = sqliteDatabase.query(CATEGORY_TABLE, columns, null, null, null, null, null);
		return cursor;
	}
	
	/*********************************************stories table********************************/
	//Inserting a story
	public long insertStory(int cat_id, String title, String content, Boolean favorite,
							int num_read, int num_liked, int num_share){
		int f = 0;
		if(favorite==true) f = 1;
		
		ContentValues contentValues = new ContentValues();
		contentValues.put(CAT_ID, 	String.valueOf(cat_id));
		contentValues.put(TITLE, 	title);
		contentValues.put(CONTENT, 	content);
		contentValues.put(FAVOURITE, 	String.valueOf(f));
		contentValues.put(NUM_READ, 	String.valueOf(num_read));
		contentValues.put(NUM_LIKED, 	String.valueOf(num_liked));
		contentValues.put(NUM_SHARED, 	String.valueOf(num_share));
		return sqliteDatabase.insert(STORY_TABLE, null, contentValues);
	}
	
	//updating a type
	public void updateStory(int id, int catid, String title, String content, Boolean favorite,
			int num_read, int num_liked, int num_share){
		int f = 0;
		if(favorite==true) f = 1;
		ContentValues contentValues = new ContentValues();
		contentValues.put(CAT_ID, 	catid);
		contentValues.put(TITLE, 	title);
		contentValues.put(CONTENT, 	content);
		contentValues.put(FAVOURITE, 	f);
		contentValues.put(NUM_READ, 	num_read);
		contentValues.put(NUM_LIKED, 	num_liked);
		contentValues.put(NUM_SHARED, 	num_share);
		String where = ID + "=" + id;
		sqliteDatabase.update(STORY_TABLE, contentValues, where, null);
	}
	
	
	public void updateStory(int id, int catid, String title, String content){
		ContentValues contentValues = new ContentValues();
		contentValues.put(CAT_ID, 	catid);
		contentValues.put(TITLE, 	title);
		contentValues.put(CONTENT, 	content);
		String where = ID + "=" + id;
		sqliteDatabase.update(STORY_TABLE, contentValues, where, null);
	}
	
	
	//updating read number
	public void updateNumRead(int id){
		ContentValues contentValues = new ContentValues();
		contentValues.put(NUM_READ, 	getNumRead(id) + 1);
		String where = ID + "=" + id;
		sqliteDatabase.update(STORY_TABLE, contentValues, where, null);
	}
		
	//updating like number
	public void updateNumLike(int id){
		ContentValues contentValues = new ContentValues();
		contentValues.put(NUM_LIKED, 	getNumLike(id)+1);
		String where = ID + "=" + id;
		sqliteDatabase.update(STORY_TABLE, contentValues, where, null);
	}
	
	//updating share number
	public void updateNumShare(int id){
		ContentValues contentValues = new ContentValues();
		contentValues.put(NUM_SHARED, 	getNumShare(id)+1);
		String where = ID + "=" + id;
		sqliteDatabase.update(STORY_TABLE, contentValues, where, null);
	}
	

	public int getNumRead(int sid){
		String[] columns = new String[]{NUM_READ};
		String where = ID+"="+sid;
		Cursor cursor = sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
		cursor.moveToFirst();
		return Integer.parseInt(cursor.getString(0));
	}
	
	public int getNumLike(int sid){
		String[] columns = new String[]{NUM_LIKED};
		String where = ID+"="+sid;
		Cursor cursor = sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
		cursor.moveToFirst();
		return Integer.parseInt(cursor.getString(0));
	}
	
	public int getNumShare(int sid){
		String[] columns = new String[]{NUM_SHARED};
		String where = ID+"="+sid;
		Cursor cursor = sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
		cursor.moveToFirst();
		return Integer.parseInt(cursor.getString(0));
	}
	

	public int deleteStory(int id){
		return sqliteDatabase.delete(STORY_TABLE, ID +"="+id, null);
	}
	
	
	public int deleteStoryByCategory(int tid){
		return sqliteDatabase.delete(STORY_TABLE, CAT_ID +"="+tid, null);
	}
	
	//deleting all stories
	public int deleteAllStories(int id){
		return sqliteDatabase.delete(STORY_TABLE, null, null);
	}
	
	//get title of a story
	public Cursor getStoryTitle(int id){
		String[] columns = new String[]{TITLE};
		String where = ID+"="+id;
		return sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
	}
	
	//get title of a story
	public Cursor getStoryContent(int id){
		String[] columns = new String[]{CONTENT};
		String where = ID+"="+id;
		Cursor cursor = sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
		return cursor;
	}

	//get title and id of all stories
	public Cursor getStories(){
		String[] columns = new String[]{ID,TITLE};
		Cursor cursor = sqliteDatabase.query(STORY_TABLE, columns, null, null, null, null, null);
		return cursor;
	}
	
	//get title and id of all stories by id of the type
	public Cursor getStories(String catid){
		String[] columns = new String[]{ID,TITLE};
		String where = CAT_ID +"="+ catid;
		Cursor cursor = sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
		return cursor;
	}
	
	//get title and id of all stories by id of the type
	public Cursor getFavouriteStoryList(){
		String[] columns = new String[]{ID,TITLE};
		String where = FAVOURITE +"="+ 1;
		Cursor cursor = sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
		return cursor;
	}
	
	//get full data of a story
	public Cursor getStoryData(int id){
		String[] columns = new String[]{ID,TITLE,CONTENT,CAT_ID, FAVOURITE,NUM_READ,NUM_LIKED,NUM_SHARED};
		String where = ID+" = "+id;
		Cursor cursor = sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
		return cursor;
	}
	
	
	
	
	public HashMap<String, String> getStory(int id){
		HashMap<String, String> data = new HashMap<String, String>();
		String[] columns = new String[]{CAT_ID, TITLE,CONTENT};
		String where = ID+" = "+id;
		Cursor cursor = sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
		cursor.moveToFirst();
		for (int i = 0; i < cursor.getCount(); i++) {
			data.put("CAT_ID", cursor.getString(0).toString());
			data.put("TITLE", cursor.getString(1).toString());
			data.put("CONTENT", cursor.getString(2).toString());
		}
		return data;
	}
	
	//Lay ve value cua cot favourite
	public Cursor getFavouriteState(int sid){
		String[] columns = new String[]{FAVOURITE};
		String where = ID+"="+sid;
		return sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
	}
	
	
	//updating favorite of the story
	public void updateFavouriteState(int id, Boolean favorite){
		int f = 0;
		if (favorite==true) f =1;
		
		ContentValues contentValues = new ContentValues();
		if (favorite == true) contentValues.put(FAVOURITE, 	1);
		else contentValues.put(FAVOURITE, 	0);
		
		String where = ID + "=" + id;
		sqliteDatabase.update(STORY_TABLE, contentValues, where, null);
	}
	
	/**
	 * Lay ve danh sach cac id truyen cua 1 loai truyen
	 * @param catid
	 * @return
	 */
	public Cursor getIds(int catid){
		String[] columns = new String[]{ID};
		String where = CAT_ID+"="+catid;
		return sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
	}
	
	
	
	
	public Cursor getFavouriteIds(){
		String[] columns = new String[]{ID};
		String where = FAVOURITE +"="+ 1;
		return sqliteDatabase.query(STORY_TABLE, columns, where, null, null, null, null);
	}
}


